# -*- coding: utf-8 -*-
__author__ = 'sunnychou'
__date__ = '2019/6/18 11:05'

'''
数据库连接使用操作封装类
采用pymssql
pip  install pymssql
'''
import pymssql
from common.logger_helper import  g_wlogger
DB_SETTINGS = {
    'HOST':'180.76.115.157',
    'USER':'sa',
    'PASSWORD':'Favor@sqladmin123',
    'DATABASE':'CRM6',
}

'''
#建立连接并获取cursor
conn = pymssql.connect(DB_SETTINGS['HOST'] , DB_SETTINGS['USER'] , DB_SETTINGS['PASSWORD'], DB_SETTINGS['DATABASE'])

cursor = conn.cursor()
sql = "select top  20 * from account"
cursor.execute(sql)
rows = cursor.fetchall()
for row in rows:
    print(row)

cursor.close()
conn.close()
'''

class MMSQLHelper(object):
    def  __init__(self, host=DB_SETTINGS['HOST'], user=DB_SETTINGS['USER'], password=DB_SETTINGS['PASSWORD'],
                  database=DB_SETTINGS['DATABASE']):
        self.__host = host
        self.__user = user
        self.__password = password
        self.__db = database
        self.__connect = self.__db_connect()


    def __del__(self):
        if self.__connect != None:
            self.__connect.close()

    def __db_connect(self):
        try:
            return pymssql.connect(self.__host, self.__user,
                                   self.__password, self.__db)
        except Exception as e:
            print(f"db_conect has error with:{e}")
            g_wlogger.werror(f"MMSQLHelper::connect has error with:{e}")
            return None

    def fetch_all(self, sql):
        '''
        查询语句执行，获取db数据库中数据
        :param sql:
        :return:
        '''
        if self.__connect == None:
            raise("连接数据库失败")
        try:
            if sql == None:
                return None
            cursor = self.__connect.cursor()
            if not cursor:
                raise ('数据库连接访问失败')
            print(f"query sql:{sql}")
            cursor.execute(sql)
            resList = cursor.fetchall()
            cursor.close()
            return resList
        except Exception as e:
            print(f"fetch_all has error with:{e}")
            g_wlogger.werror(f"MMSQLHelper::fetch_all has error with:{e}")
            return None

    def db_excuete(self, co_sql):
        '''
        非查询类，插入，更新，删除语句
        :param co_sql: 语句
        :return:
        '''
        if self.__connect == None:
            raise("连接数据库失败")
        cursor = self.__connect.cursor()
        try:
            cursor.execute(co_sql)
            self.__connect.commit()
        except Exception as e:
            self.__connect.rollback()
            g_wlogger.werror(f"MMSQLHelper::db_excuete has error with:{e}")
        finally:
            cursor.close()


    def db_excuete_many(self, co_sql, record_list):
        '''
        非查询类，插入，更新，删除语句
        :param co_sql: 语句
        :param record_list:tuple 列表
        :return:
        '''
        if self.__connect == None:
            raise("连接数据库失败")
        cursor = self.__connect.cursor()
        try:
            cursor.executemany(co_sql, record_list)
            self.__connect.commit()
        except Exception as e:
            print(f"db_excuete_many has error with:{e}")
            self.__connect.rollback()
        finally:
            cursor.close()


g_msql_inst = MMSQLHelper()